﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Web;
using SHNOPData.common;

namespace Sysmng
{
    public static class SysmngDB
    {
        #region 获取用户信息列表
        /// <summary>
        /// 获取用户信息列表
        /// </summary>
        /// <param name="SearchType"></param>
        /// <param name="Content"></param>
        /// <returns></returns>
        public static DataTable GetUserInfoList(int SearchType, string Content)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = "MTNOH_SH_APP.dbo.mtsp_查询_用户列表信息";
                int count = 2;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@SearchType", SearchType);
                sp[--count] = new SqlParameter("@Content", Content);
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, commandText,sp);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 加载用户详细信息界面信息
        /// <summary>
        /// 加载用户详细信息界面信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetUserBaseInfo()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = @"select role_id as ID,role_name  as Name  
                                     from MTNOH_SH_APP.dbo.C_ROLE";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.Text, commandText);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 删除用户
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static string DeleteUserBaseInfo(string UserID)
        {
                string rst="";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return rst="数据库连接失败。";
            }
            try
            {
                string commandText = @"update MTNOH_SH_APP.dbo.T_User 
                                    set Flag=0 
                                    where ID='" + UserID + "'";
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
                if(a!=null && a<0)
                {
                    rst = "删除失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                return rst = e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 编辑用户信息
        public static string EditUserInfo(UserInfoItem userInfo)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = "MTNOH_SH_APP.dbo.mtsp_编辑_用户信息";
                int count = 6;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@UserId", userInfo.UserId);
                sp[--count] = new SqlParameter("@Name", userInfo.Name);
                sp[--count] = new SqlParameter("@UserName", userInfo.UserName);
                sp[--count] = new SqlParameter("@Role_id", userInfo.Role_id == string.Empty ? null : userInfo.Role_id);
                sp[--count] = new SqlParameter("@Phone", userInfo.Phone);
                sp[--count] = new SqlParameter("@Email", userInfo.Email);
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, sp);
                if (a < 0)
                {
                    rst = "编辑数据失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                return e.Message.ToString() ;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 查询角色列表
        /// <summary>
        /// 查询角色列表
        /// </summary>
        /// <param name="RoleName"></param>
        /// <returns></returns>
        public static DataTable GetRoleInfoList(string RoleName)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = @"select role_id,role_name,role_desc 
                                      from MTNOH_SH_APP.dbo.C_ROLE 
                                      where role_name like '%" + RoleName + "%'";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.Text, commandText);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 删除角色信息
        /// <summary>
        /// 删除角色信息
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public static string DeleteRoleBaseInfo(int RoleID)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = @"delete from MTNOH_SH_APP.dbo.C_ROLE 
                                        where role_id=" + RoleID
                                        + @" delete from MTNOH_SH_APP.dbo.C_Role_Menu 
                                        where role_id=" + RoleID
                                        + @" delete from MTNOH_SH_APP.dbo.C_Role_User 
                                        where roleid=" + RoleID;
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
                if (a != null && a < 0)
                {
                    rst = "删除失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                return rst = e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 编辑角色信息
        public static string EditRoleInfo(RoleInfoItem roleInfo)
        {
            string rst = "";
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return rst = "数据库连接失败。";
            }
            try
            {
                string commandText = "MTNOH_SH_APP.dbo.mtsp_编辑_角色信息";
                int count = 4;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@RoleId", roleInfo.RoleId);
                sp[--count] = new SqlParameter("@RoleName", roleInfo.RoleName);
                sp[--count] = new SqlParameter("@RoleDescribe", roleInfo.RoleDescribe);
                sp[--count] = new SqlParameter("@MenuIdsStr", roleInfo.MenuIdsStr);
                int a = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, sp);
                if (a < 0)
                {
                    rst = "编辑数据失败。";
                }
                return rst;
            }
            catch (Exception e)
            {
                return e.Message.ToString();
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion 

        #region 获取目录列表，用于加载角色分配目录树
        /// <summary>
        /// 获取目录列表，用于加载角色分配目录树
        /// </summary>
        /// <returns></returns>
        public static DataTable GetMenuList()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = @"select id,parentId,menu_name,menu_desc,menu_Id 
                                        from MTNOH_SH_APP.dbo.C_Menu";
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.Text, commandText);
                DataTable dt = new DataTable();
                ad.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion

        #region 获取角色基础信息，填充角色详情页面
        /// <summary>
        /// 获取角色基础信息，填充角色详情页面
        /// </summary>
        /// <returns></returns>
        public static DataSet GetRoleBaseInfo(int RoleId)
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = "MTNOH_SH_APP.dbo.mtsp_查询_角色详细信息";
                int count = 1;
                SqlParameter[] sp = new SqlParameter[count];
                sp[--count] = new SqlParameter("@RoleId", RoleId);
                SqlDataAdapter ad = SqlHelper.ExecuteDataAdapter(conn, CommandType.StoredProcedure, commandText, sp);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        /// <summary>
        /// 获取系统字典表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDicTable()
        {
            SqlConnection conn = ConnMng.GetConnection();
            if (conn == null || conn.State != System.Data.ConnectionState.Open)
            {
                return null;
            }
            try
            {
                string commandText = @"select t.ID,t.ParentID,t.Name,t.Description,t.OrderID,t.ParentName from T_Dictionary t";
                DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText);
                return ds.Tables[0];
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                ConnMng.ReleaseConnection(conn);
            }
        }
        #endregion
    }
}
